package com.gss.armory.persistence;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;

import com.gss.armory.form.DocReports;
import com.gss.armory.form.DocumentCustodianDetailsForm;
import com.gss.armory.form.ReportsForm;
import com.gss.common.util.ApplicationConstants;
import com.gss.common.util.ApplicationUtilities;
import com.gss.security.domain.RoleDetailsDTO;
import com.gss.security.domain.UserDetailsDTO;


@Repository
public class ReportsDAO extends HibernateDaoSupport {

	private Logger logger = Logger.getLogger(ReportsDAO.class);
	
	@Autowired
	public SessionFactory sessionFactory;
	
	@Autowired
	public ReportsDAO(SessionFactory sessionFactory){
		super.setSessionFactory(sessionFactory);
		logger.debug("inside reports dao");
	}
	
	public List returnReports(ReportsForm reportsForm) throws Exception {
		
		/*String hqlquery1 = "select general.docId, general.docName, general.docTypeId, general.docCategoryId, general.docSubCategoryId ,general.strCreatedAuthor, general.docOwnerName," +
				" general.docDepttId, general.docLocId, general.renewable, link.linkedDocId" +
				" from DocGeneralDetailsDTO as general, DocumentLinkDTO as link" +
				" where (general.docId = link.parentDocId)";*/
		
		//Changed by Chandan on 04-08-2012 to include Company Id check
		
		String hqlquery1 = "select general.docId," +			//0
							" general.docName," +				//1
							" general.docTypeId," +				//2
							" general.docCategoryId," +			//3
							" general.docSubCategoryId," +		//4
							" general.strCreatedAuthor," +		//5
							" general.docOwnerName," +			//6
							" general.docDepttId," +			//7
							" general.docLocId," +				//8
							" general.renewable," +				//9
							" link.linkedDocId" +				//10
							" from DocGeneralDetailsDTO as general, DocumentLinkDTO as link" +
							" where (general.docId = link.parentDocId) and docCompanyId = :userCompanyId";
		
		//stringBuffer.append(hqlquery1);	 AND" +	" (concat(general.docTypeId,'#',general.docLocId,'#',general.docDepttId) = revapp.masterCode)	 OR (general.docId = link.parentDocId) OR (general.docId = link.linkedDocId) 
		
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		List result = null;
		try {
			Query query = session.createSQLQuery(hqlquery1);
			query.setParameter("userCompanyId", ApplicationUtilities.getCurrentUserCompanyId());
			result = query.list();
			for (Object object : result) {}
		} 
		catch (Exception e) {
			e.printStackTrace();
			result = new ArrayList();
		}
		finally{
			if(session.isOpen())
				session.close();
		}
		return result;
	}

	public int fetchReportsAccToBU(long buId){
		UserDetailsDTO userDetailsDTO = (UserDetailsDTO) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		List<GrantedAuthority> authorities = (List<GrantedAuthority>) userDetailsDTO.getAuthorities();
		List<String> list = new ArrayList<String>();
		int result = 0;
		for(GrantedAuthority authority:authorities)
		{
			RoleDetailsDTO detailsDTO = (RoleDetailsDTO) authority;
			list.add(detailsDTO.getRoleId());
		}
		
		if(list.contains(ApplicationConstants.ROLE_CENTRAL_CONTROLLER) || list.contains(ApplicationConstants.ROLE_OWNER)){
			if(!(ApplicationConstants.ALL_LONG==buId)){
				result = 3;	//form's location
		    }
			else{
		    	result = 4; //for ALL locations
		    }
		}
		else /*if(list.contains(ApplicationConstants.ROLE_CONTROLLER) || list.contains(ApplicationConstants.ROLE_REVIEWER) 
				|| list.contains(ApplicationConstants.DEFAULT_ROLE))*/{
			if(!(ApplicationConstants.ALL_LONG==buId)){
			 	if(userDetailsDTO.getBuId() == buId){
			 		result = 1; //user's location
			 	}else{
			 		result = 2;	//no location
			 	}
		    }else{
		    	result = 1;	//user's location
		    }
		}
		return result;
	}
		
		
	public List<Object[]> returnExpiredDocReports(DocReports docReports) throws Exception {
		String authSignId = docReports.getAuthSignId();
		long buId = docReports.getDocLocId();
		long depttId = docReports.getDocDepttId();
		int typeId = docReports.getDocTypeId();
		int categId = docReports.getDocCategoryId();
		int subcategId = docReports.getDocSubCategoryId();
		UserDetailsDTO userDetailsDTO = (UserDetailsDTO) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
			
		StringBuffer stringBuffer = new StringBuffer();
		
		stringBuffer.append("select DGD_DOCCODE," +		//0
				" DGD_DOC_NAME," +						//1
				" DGD_DOC_TYPE_ID," +					//2
				" DGD_CATEGORY_ID," +					//3
				" DGD_SUBCATEGORY_ID," +				//4
				" CREATED_AUTHOR," +					//5
				" DGD_DOC_OWNER_NAME," +				//6
				" DGD_DOC_DEPTT_ID," +					//7
				" DGD_DOC_LOC_ID," +					//8
				" DGD_DOC_VALIDITY_START_DATE," +		//9
				" DGD_DOC_VALIDITY_END_DATE," +			//10
				" DASD_USER_ID," +						//11
				" DGD_ID," +							//12
				" DGD_ISSUING_AUTH," +					//13
				" DGD_ENTERED_WITH,"+					//14
				" DOC_COMPANY_ID"+						//15
				" from DOC_GENERAL_DETAILS LEFT OUTER JOIN DOCUMENT_AUTH_SIGN_DETAILS" +
				" ON DOC_GENERAL_DETAILS.DGD_ID = DOCUMENT_AUTH_SIGN_DETAILS.DASD_DOC_ID" +
				" where DOC_GENERAL_DETAILS.DGD_DOC_VALIDITY_END_DATE < :currentdate AND DOC_GENERAL_DETAILS.DGD_DOCCODE" +
				" IS NOT NULL");
			
		
		int result = fetchReportsAccToBU(docReports.getDocLocId());
		if(result < 4)
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_LOC_ID = :buId");
		
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getAuthSign())){
			stringBuffer.append(" AND DOCUMENT_AUTH_SIGN_DETAILS.DASD_USER_ID = :authSignId AND DOC_GENERAL_DETAILS.DGD_ID = DOCUMENT_AUTH_SIGN_DETAILS.DASD_DOC_ID");
		}
	//		if(docReports.getDocLocId() != ApplicationConstants.ALL_LONG){
	//			int result = fetchReportsAccToBU(docReports.getDocLocId());
	//			if(result < 4)
	//				stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_LOC_ID = :buId");
	//		}
		if(docReports.getDocDepttId() != ApplicationConstants.ALL_LONG){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_DEPTT_ID = :depttId");
		}
		if(docReports.getDocTypeId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_TYPE_ID = :typeId");
		}
		if(docReports.getDocCategoryId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_CATEGORY_ID = :categId");
		}
		if(docReports.getDocSubCategoryId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_SUBCATEGORY_ID = :subcategId");
		}
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocIssuingAuthority())){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_ISSUING_AUTH like :docIssuingAuthority");
		}
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocEnteredWith())){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_ENTERED_WITH like :docEnteredWith");
		}
		if(null != docReports.getDocCompanyId() 
				&& ApplicationConstants.ALL_INT.intValue() != docReports.getDocCompanyId().intValue()) {
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DOC_COMPANY_ID = :docCompanyId");
		}
		
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		List<Object[]> resultList = null;
		try {
			Query query = session.createSQLQuery(stringBuffer.toString());
			
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getAuthSign())){
				query.setParameter("authSignId", authSignId);
			}
		//		if(docReports.getDocLocId() != ApplicationConstants.ALL_LONG){
		//			int result2 = fetchReportsAccToBU(docReports.getDocLocId());
				if(result == 1){
					query.setParameter("buId", userDetailsDTO.getBuId());
				}
				else if(result == 2){
					query.setParameter("buId", 0l);
				}
				else if(result == 3){
					query.setParameter("buId", buId);
				}
		//		}
			if(docReports.getDocDepttId() != ApplicationConstants.ALL_LONG){
				query.setParameter("depttId", depttId);
			}
			if(docReports.getDocTypeId() != ApplicationConstants.ALL_INT){
				query.setParameter("typeId", typeId);
			}
			if(docReports.getDocCategoryId() != ApplicationConstants.ALL_INT){
				query.setParameter("categId", categId);
			}
			if(docReports.getDocSubCategoryId() != ApplicationConstants.ALL_INT){
				query.setParameter("subcategId", subcategId);
			}
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocIssuingAuthority())){
				query.setParameter("docIssuingAuthority", "%" + docReports.getDocIssuingAuthority() + "%");
			}
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocEnteredWith())){
				query.setParameter("docEnteredWith", "%" + docReports.getDocEnteredWith() + "%");
			}
			if(null != docReports.getDocCompanyId() 
					&& ApplicationConstants.ALL_INT.intValue() != docReports.getDocCompanyId().intValue()) {
				query.setParameter("docCompanyId", docReports.getDocCompanyId());
			}
				
			Calendar calendar = Calendar.getInstance();
			query.setParameter("currentdate", calendar.getTime());
			
			resultList = query.list();
			for (Object[] objects : resultList) {}
		} 
		catch (Exception e) {
			e.printStackTrace();
			resultList = new ArrayList<Object[]>();
		}
		finally{
			if(session.isOpen())
				session.close();
		}
		
		return resultList;
	}
	
	
	public List<Object[]> returnNearExpiryDocReports(DocReports docReports) throws Exception {
		String authSignId = docReports.getAuthSignId();
		long buId = docReports.getDocLocId();
		long depttId = docReports.getDocDepttId();
		int typeId = docReports.getDocTypeId();
		int categId = docReports.getDocCategoryId();
		int subcategId = docReports.getDocSubCategoryId();
		UserDetailsDTO userDetailsDTO = (UserDetailsDTO) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuffer stringBuffer = new StringBuffer();
		
		stringBuffer.append("select DGD_DOCCODE," +					//0
							" DGD_DOC_NAME," +						//1
							" DGD_DOC_TYPE_ID," +					//2
							" DGD_CATEGORY_ID," +					//3
							" DGD_SUBCATEGORY_ID," +				//4
							" CREATED_AUTHOR," +					//5
							" DGD_DOC_OWNER_NAME," +				//6
							" DGD_DOC_DEPTT_ID," +					//7
							" DGD_DOC_LOC_ID," +					//8
							" DGD_DOC_VALIDITY_START_DATE," +		//9
							" DGD_DOC_VALIDITY_END_DATE," +			//10
							" DASD_USER_ID," +						//11
							" DGD_ID," +							//12
							" DGD_ISSUING_AUTH," +					//13
							" DGD_ENTERED_WITH,"+					//14
							" DOC_COMPANY_ID"+						//15
							" from DOC_GENERAL_DETAILS LEFT OUTER JOIN DOCUMENT_AUTH_SIGN_DETAILS" +
							" ON DOC_GENERAL_DETAILS.DGD_ID = DOCUMENT_AUTH_SIGN_DETAILS.DASD_DOC_ID" +
							" where DOC_GENERAL_DETAILS.DGD_ISRENEWABLE = '1' AND DOC_GENERAL_DETAILS.DGD_DOC_VALIDITY_END_DATE > :startDate" +
							" AND DOC_GENERAL_DETAILS.DGD_DOC_VALIDITY_END_DATE < :endDate AND DOC_GENERAL_DETAILS.DGD_DOCCODE IS NOT NULL AND " +
							"(DOC_GENERAL_DETAILS.DGD_DOC_STATUS = 'Active' or " +
							" DOC_GENERAL_DETAILS.DGD_DOC_STATUS = 'Due' or DOC_GENERAL_DETAILS.DGD_DOC_STATUS = 'Overdue') ");
		
	
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getAuthSign())){
			stringBuffer.append(" AND DOCUMENT_AUTH_SIGN_DETAILS.DASD_USER_ID = :authSignId ");
		}
	//		if(docReports.getDocLocId() != ApplicationConstants.ALL_LONG){
			int result = fetchReportsAccToBU(docReports.getDocLocId());
			if(result < 4)
				stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_LOC_ID = :buId");
	//		}
		if(docReports.getDocDepttId() != ApplicationConstants.ALL_LONG){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_DEPTT_ID = :depttId");
		}
		if(docReports.getDocTypeId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_TYPE_ID = :typeId");
		}
		if(docReports.getDocCategoryId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_CATEGORY_ID = :categId");
		}
		if(docReports.getDocSubCategoryId() != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_SUBCATEGORY_ID = :subcategId");
		}
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocIssuingAuthority())){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_ISSUING_AUTH like :docIssuingAuthority");
		}
		if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocEnteredWith())){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_ENTERED_WITH like :docEnteredWith");
		}
		if(null != docReports.getDocCompanyId()
				&& ApplicationConstants.ALL_INT.intValue() != docReports.getDocCompanyId().intValue()) {
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DOC_COMPANY_ID = :docCompanyId");
		}
	
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		List<Object[]> resultList = null;
		try {
			Query query = session.createSQLQuery(stringBuffer.toString());
			
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getAuthSign())){
				query.setParameter("authSignId", authSignId);
			}
		//		if(docReports.getDocLocId() != ApplicationConstants.ALL_LONG){
		//			int result2 = fetchReportsAccToBU(docReports.getDocLocId());
				if(result == 1){
					query.setParameter("buId", userDetailsDTO.getBuId());
				}
				else if(result == 2){
					query.setParameter("buId", 0l);
				}
				else if(result == 3){
					query.setParameter("buId", buId);
				}
		//		}
			if(docReports.getDocDepttId() != ApplicationConstants.ALL_LONG){
				query.setParameter("depttId", depttId);
			}
			if(docReports.getDocTypeId() != ApplicationConstants.ALL_INT){
				query.setParameter("typeId", typeId);
			}
			if(docReports.getDocCategoryId() != ApplicationConstants.ALL_INT){
				query.setParameter("categId", categId);
			}
			if(docReports.getDocSubCategoryId() != ApplicationConstants.ALL_INT){
				query.setParameter("subcategId", subcategId);
			}
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocIssuingAuthority())){
				query.setParameter("docIssuingAuthority", "%" + docReports.getDocIssuingAuthority() + "%");
			}
			if(!ApplicationUtilities.checkIfNullOrBlank(docReports.getDocEnteredWith())){
				query.setParameter("docEnteredWith", "%" + docReports.getDocEnteredWith() + "%");
			}
			if(null != docReports.getDocCompanyId() 
					&& ApplicationConstants.ALL_INT.intValue() != docReports.getDocCompanyId().intValue()) {
				query.setParameter("docCompanyId", docReports.getDocCompanyId());
			}
			
			Calendar calendar = Calendar.getInstance();
			query.setParameter("startDate", calendar.getTime());
			
			calendar.add(Calendar.DAY_OF_MONTH, 30);
			query.setParameter("endDate", calendar.getTime());
						
			resultList = query.list();
			for (Object[] objects : resultList) {}
		} 
		catch (Exception e) {
			e.printStackTrace();
			resultList = new ArrayList<Object[]>();
		}
		finally{
			if(session.isOpen())
				session.close();
		}
		
		return resultList;
	}
	
	
	public List<Object[]> returnVaultDetailsReports(DocumentCustodianDetailsForm detailsForm) throws Exception {
		long buId = detailsForm.getDocLocId();
		long depttId = detailsForm.getDocDepttId();
		int typeId = detailsForm.getDocTypeId();
		int categId = detailsForm.getDocCategoryId();
		int subcategId = detailsForm.getDocSubCategoryId();
		Long buIdCust = detailsForm.getCustodianLocId();
		Long depttIdCust = detailsForm.getCustodianDepttId();
		UserDetailsDTO userDetailsDTO = (UserDetailsDTO) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuffer stringBuffer = new StringBuffer();
		
		stringBuffer.append("select DOC_GENERAL_DETAILS.DGD_DOCCODE," +							//0
							" DOC_GENERAL_DETAILS.DGD_DOC_NAME," +								//1
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_VAULT_NUMBER," +					//2
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_LOCATION," +				//3
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_NAME," +					//4
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_DESIGNATION," +			//5
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_LOCATION_ID," +			//6
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_DEPTT_ID," +				//7
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_ID," +					//8
							" DOC_GENERAL_DETAILS.DGD_DOC_TYPE_ID," +							//9
							" DOC_GENERAL_DETAILS.DGD_CATEGORY_ID," +							//10
							" DOC_GENERAL_DETAILS.DGD_SUBCATEGORY_ID," +						//11
							" DOCUMENT_CUSTODIAN_DETAILS.DCD_DOC_ID," +							//12
							" DOC_GENERAL_DETAILS.DOC_COMPANY_ID" +								//13
							" from DOC_GENERAL_DETAILS LEFT OUTER JOIN" +
							" DOCUMENT_CUSTODIAN_DETAILS ON DOC_GENERAL_DETAILS.DGD_ID = DOCUMENT_CUSTODIAN_DETAILS.DCD_DOC_ID" +
							" where DOC_GENERAL_DETAILS.DGD_DOCCODE IS NOT NULL AND DOC_GENERAL_DETAILS.DGD_DOC_STATUS = 'Active' ");
		
		int result = fetchReportsAccToBU(detailsForm.getDocLocId());
		if(result < 4)
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_LOC_ID = :buId");

		if(depttId != ApplicationConstants.ALL_LONG){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_DEPTT_ID = :depttId");
		}
		if(typeId != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_DOC_TYPE_ID = :typeId");
		}
		if(categId != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_CATEGORY_ID = :categId");
		}
		if(subcategId != ApplicationConstants.ALL_INT){
			stringBuffer.append(" AND DOC_GENERAL_DETAILS.DGD_SUBCATEGORY_ID = :subcategId");
		}
		if(buIdCust != ApplicationConstants.ALL_LONG){
			stringBuffer.append(" AND DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_LOCATION_ID = :buIdCust");
		}
		if(depttIdCust != ApplicationConstants.ALL_LONG){
			stringBuffer.append(" AND DOCUMENT_CUSTODIAN_DETAILS.DCD_CUSTODIAN_DEPTT_ID = :depttIdCust");
		}
	
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		List<Object[]> resultList = null;
		try {
			Query query = session.createSQLQuery(stringBuffer.toString());
			
			//		if(buId != ApplicationConstants.ALL_LONG){
			//			int result2 = fetchReportsAccToBU(detailsForm.getDocLocId());
					if(result == 1){
						query.setParameter("buId", userDetailsDTO.getBuId());
					}
					else if(result == 2){
						query.setParameter("buId", 0l);
					}
					else if(result == 3){
						query.setParameter("buId", buId);
					}
			//		}
				if(depttId != ApplicationConstants.ALL_LONG){
					query.setParameter("depttId", depttId);
				}
				if(typeId != ApplicationConstants.ALL_INT){
					query.setParameter("typeId", typeId);
				}
				if(categId != ApplicationConstants.ALL_INT){
					query.setParameter("categId", categId);
				}
				if(subcategId != ApplicationConstants.ALL_INT){
					query.setParameter("subcategId", subcategId);
				}
				if(buIdCust != ApplicationConstants.ALL_LONG){
					query.setParameter("buIdCust", buIdCust);
				}
				if(depttIdCust != ApplicationConstants.ALL_LONG){
					query.setParameter("depttIdCust", depttIdCust);
				}
				
				 resultList = query.list();
				 for (Object[] objects : resultList) {}
		} 
		catch (Exception e) {
			e.printStackTrace();
			resultList = new ArrayList<Object[]>();
		}
		finally{
			if(session.isOpen())
				session.close();
		}
		return resultList;
	}
}